from code.data.Comment import Comment
from code.dao.user_management import __check_user_exist
from code.dao.subject_management import __check_subject_exist
from code.dao.config import mysql_conn, cursor

import logging
import pymysql
import traceback
import time
import datetime


def add_comment(comment: Comment):
    if comment.userID is None:
        logging.error("UserID is required.")
        return False
    if not __check_user_exist(comment.userID):
        return False
    if comment.subjectID is None:
        logging.error("SubjectID is required.")
        return False
    if not __check_subject_exist(comment.subjectID):
        return False
    if comment.content is None:
        logging.error("Content is required.")
        return False
    if comment.reply is None:
        logging.error("Reply is required.")
        return False
    sql = "INSERT INTO comment(content, reply, userID, subjectID) VALUES ('{}', {}, '{}', {})".format(
        comment.content, comment.reply, comment.userID, comment.subjectID)
    try:
        cursor.execute(sql)
        mysql_conn.commit()
    except pymysql.Error as e:
        logging.error("Inserting comment into database failed: " + str(e.args[1]))
        mysql_conn.rollback()
        return False
    logging.info("Comment is added with SQL: " + sql)
    return True


def search_comment(comment: Comment, search_by: str = "all", fuzzy_search=False):
    comments = []
    if search_by == "all":
        sql = "SELECT * FROM comment"
    elif search_by == "commentID":
        if comment.commentID is None:
            logging.error("CommentID is required.")
            return False
        sql = "SELECT * FROM comment WHERE commentID = {}".format(comment.commentID)
    elif search_by == "time":
        if comment.time is None:
            logging.error("Time is required.")
            return False
        sql = "SELECT * FROM comment WHERE time = '{}'".format(comment.time)
        if fuzzy_search:
            sql = "SELECT * FROM comment WHERE time LIKE '%{}%'".format(comment.time)
    elif search_by == "content":
        if comment.content is None:
            logging.error("Content is required.")
            return False
        sql = "SELECT FROM comment WHERE content = '{}'".format(comment.content)
        if fuzzy_search:
            sql = "SELECT * FROM comment WHERE content LIKE '%{}%'".format(comment.content)
    elif search_by == "reply":
        if comment.reply is None:
            logging.error("Reply is required.")
            return False
        sql = "SELECT * FROM comment WHERE reply = {}".format(comment.reply)
    elif search_by == "userID":
        if comment.userID is None:
            logging.error("UserID is required.")
            return False
        sql = "SELECT * FROM comment WHERE userID = {}".format(comment.userID)
    elif search_by == "subjectID":
        if comment.subjectID is None:
            logging.error("SubjectID is required.")
            return False
        sql = "SELECT * FROM comment WHERE subjectID = {}".format(comment.subjectID)
    else:
        logging.error("Attribute for search_by is not exist, use one in "
                      '["all", "commentID", "time", "content", "reply", "userID", "subjectID"]')
        return None
    try:
        cursor.execute(sql)
        results = cursor.fetchall()
        if not results:
            logging.warning("Comment is not exist.")
            return None
    except pymysql.Error as e:
        logging.error("Searching for comment failed: " + str(e.args[1]))
        traceback.print_exc()
        return None
    for result in results:
        comments.append(Comment(result[0], result[1], result[2], result[3], result[4], result[5]))
    logging.info("Search comment(s) with SQL: {}".format(sql))
    return comments


def search_comment_by_subjectID(comment: Comment, search_by: str = "all", fuzzy_search=False):
    if comment.subjectID is None:
        logging.error("SubjectID is required.")
        return None
    if not __check_subject_exist(comment.subjectID):
        return None
    comments = []
    if search_by == "all":
        sql = "SELECT * FROM comment WHERE subjectID = {}".format(comment.subjectID)
    elif search_by == "commentID":
        if comment.commentID is None:
            logging.error("CommentID is required.")
            return False
        sql = "SELECT * FROM comment WHERE commentID = {} AND subjectID = {}".\
            format(comment.commentID, comment.subjectID)
    elif search_by == "time":
        if comment.time is None:
            logging.error("Time is required.")
            return False
        sql = "SELECT * FROM comment WHERE time = '{}' AND subjectID = {}".format(comment.time, comment.subjectID)
        if fuzzy_search:
            sql = "SELECT * FROM comment WHERE time LIKE '%{}%' AND subjectID = {}".\
                format(comment.time, comment.subjectID)
    elif search_by == "content":
        if comment.content is None:
            logging.error("Content is required.")
            return False
        sql = "SELECT * FROM comment WHERE content = '{}' AND subjectID = {}".format(comment.content, comment.subjectID)
        if fuzzy_search:
            sql = "SELECT * FROM comment WHERE content LIKE '%{}%' AND subjectID = {}".\
                format(comment.content, comment.subjectID)
    elif search_by == "reply":
        if comment.time is None:
            logging.error("Reply is required.")
            return False
        sql = "SELECT * FROM comment WHERE reply = {} AND subjectID = {}".format(comment.reply, comment.subjectID)
    elif search_by == "userID":
        if comment.userID is None:
            logging.error("UserID is required.")
            return False
        sql = "SELECT * FROM comment WHERE userID = {} AND subjectID = {}".format(comment.userID, comment.subjectID)
    else:
        logging.error("Attribute for search_by is not exist, use one in "
                      '["all", "commentID", "time", "content", "reply", "userID"]')
        return None
    try:
        cursor.execute(sql)
        results = cursor.fetchall()
        if not results:
            logging.warning("Comment is not exist.")
            return None
    except pymysql.Error as e:
        logging.error("Searching for comment failed: " + str(e.args[1]))
        traceback.print_exc()
        return None
    for result in results:
        comments.append(Comment(result[0], result[1], result[2], result[3], result[4], result[5]))
    logging.info("Search comment(s) with SQL: {}".format(sql))
    return comments


def delete_comment(comment: Comment, delete_by: str):
    if delete_by == "commentID":
        if comment.commentID is None:
            logging.error("CommentID is required.")
            return False
        if not __check_comment_exist(comment.commentID):
            return False
        sql = "DELETE FROM comment WHERE commentID = {}".format(comment.commentID)
    if delete_by == "subjectID":
        if comment.subjectID is None:
            logging.error("SubjectID is required.")
            return False
        sql = "DELETE FROM comment WHERE subjectID = {}".format(comment.subjectID)
    try:
        cursor.execute(sql)
        mysql_conn.commit()
    except pymysql.Error as e:
        logging.error("Delete subject from database failed: " + str(e.args[1]))
        mysql_conn.rollback()
        return False
    logging.info("Subject is deleted with SQL: {}".format(sql))
    return True


def __check_comment_exist(commentID: int):
    sql = "SELECT * FROM comment WHERE commentID = {}".format(commentID)
    try:
        cursor.execute(sql)
        results = cursor.fetchall()
        if not results:
            logging.error("Comment {} is not exist".format(commentID))
            return False
    except pymysql.Error as e:
        logging.error("Searching for comment failed: " + str(e.args[1]))
        traceback.print_exc()
        return False
    return True


if __name__ == "__main__":
    comment1 = Comment(content="This is a comment.", reply=0, userID="admin", subjectID=2)
    print(add_comment(comment1))
    results1 = search_comment(Comment(reply=0), "reply")
    for result in results1:
        print(result.to_string())
    comment2 = Comment(content="This is a reply to comment.", reply=results1[0].commentID, userID="admin", subjectID=2)
    print(add_comment(comment2))
    results2 = search_comment(Comment(content="This"), "content", True)
    for result in results2:
        print(result.to_string())
    print(delete_comment(results1[0], "commentID"))
    print(delete_comment(results1[0], "subjectID"))
